-- EXEC SP_SEARCH_INVENTORY_PACKING_SLIP '0',0,0,0,'10/29/2009', '10/29/2010'

ALTER PROC SP_SEARCH_INVENTORY_PACKING_SLIP( @PS_TYPE VARCHAR(50), @ProcessStep_ID INT, @From_Vendor INT, @To_Vendor INT, @From_Date Varchar(20), @To_Date VARCHAR(20) )
AS
BEGIN
	DECLARE @SQLString NVARCHAR(2000)
	DECLARE @ParamDefinition NVARCHAR(500)
	CREATE TABLE #results_table ( PackingSlip_Id INT, PackingSlip_No VARCHAR(50), FROM_Location VARCHAR(50), TO_Location VARCHAR(100), Ship_Date VARCHAR(20), Status VARCHAR(50), PS_TYPE VARCHAR(50)  )
	
	SET @SQLString = N'INSERT INTO #results_table (PackingSlip_Id, PackingSlip_No, FROM_Location, TO_Location, Ship_Date, Status ,PS_TYPE ) 
			SELECT IPT.PackingSlip_ID, PackingSlip_No, ( SELECT V_Name FROM VENDOR WHERE Vendor_Id = IPT.From_Vendor_ID )
	,( SELECT V_Name FROM VENDOR WHERE Vendor_Id = IPT.To_Vendor_ID ), IPT.Ship_Date, IPT.STATUS, PS.PS_TYPE 
	FROM Inventory_PackingSlip_Table IPT, Inventory_PackingSlip_lot_Table IPLT, Lot_Table LT, PROCESS_STEP PS WHERE 
	IPT.PackingSlip_ID = IPLT.PackingSlip_ID AND IPLT.Lot_ID = Lt.Lot_ID AND LT.ProcessStep_ID = PS.ProcessStep_ID '

	IF @PS_TYPE <> '0'
	BEGIN
		SET @SQLString = @SQLString + ' AND PS.PS_TYPE = '''+ @PS_TYPE +''''
	END
	IF @ProcessStep_ID <> 0
	BEGIN
		SET @SQLString = @SQLString + ' AND Lt.ProcessStep_ID = ' +CONVERT( VARCHAR,  @ProcessStep_ID )
	END
	IF @From_Vendor <> 0
	BEGIN
		SET @SQLString = @SQLString + ' AND IPT.From_Vendor_ID = '+ CONVERT( VARCHAR, @From_Vendor )
	END
	IF @To_Vendor  <> 0
	BEGIN
		SET @SQLString = @SQLString + ' AND IPT.To_Vendor_ID = ' + CONVERT( VARCHAR, @To_Vendor )
	END
	IF @From_Date IS NOT NULL AND LTRIM ( RTRIM ( @From_Date ) ) <> '' AND LTRIM ( RTRIM ( @From_Date ) ) <> '0'
	BEGIN
		SET @SQLString = @SQLString + ' AND CONVERT( DATETIME, IPT.Ship_Date ) >= ''' + @From_Date + ''''
	END
	IF @To_Date IS NOT NULL AND LTRIM ( RTRIM ( @To_Date ) ) <> '' AND LTRIM ( RTRIM ( @To_Date ) ) <> '0'
	BEGIN
		SET @SQLString = @SQLString + ' AND CONVERT(DATETIME, IPT.Ship_Date ) <= ''' + @To_Date + ''''
	END
	
	SET @SQLString = @SQLString + ' ORDER BY IPT.Ship_Date DESC'

	--PRINT @SQLString

	execute sp_executesql @SQLString 
	SELECT Distinct PackingSlip_ID, PackingSlip_No, FROM_Location, To_Location, Ship_Date, Status, PS_TYPE FROM #results_table
END